package com.base.user;

import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.additional.query.impl.LambdaQueryChainWrapper;
import com.base.user.entity.User;
import com.base.user.mapper.UserMapper;

@SpringBootTest
@RunWith(SpringRunner.class)
public class UserTest1 {

	@Autowired
	private UserMapper userMapper;
	
	/**
	 * 查询全部
	 */
	@Test
    public void selectList(){
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }
	
	/**
	 * 添加一条数据
	 */
	@Test
    public void insert(){
        User user = new User();
        user.setAge(31);
        user.setManagerId(1088250446457389058L);
        user.setCreateTime(LocalDateTime.now());
        int insert = userMapper.insert(user);
        System.out.println("影响记录数："+insert);
        Long id = user.getId();
        System.out.println("添加成功以后主键:" + id);
    }

	/**
          * 查询名字中包含'雨'并且年龄小于40
     * where name like '%雨%' and age < 40
     * like      '%雨%
     * likeLeft  '%雨'
     * likeRight '雨%'
     */
    @Test
    public void selectByWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","雨").lt("age",40);
        //SELECT id,name,manager_id,email,age FROM user WHERE (name LIKE ? AND age < ?) 
        //%雨%(String), 40(Integer)
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

    
    /**
          * 嵌套查询
          * 创建日期为2019年2月14日并且直属上级姓名为王姓
     * date_format(create_time,'%Y-%m-%d') and manager_id in (select id from user where name like '王%')
     */
    @Test
    public void selectByWrapper2(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //下面的日期查询使用的是占位符的形式进行查询，目的就是为了防止SQL注入的风险。
        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-10-02")
                .inSql("manager_id","select id from user where name like '王%'");
        //SELECT id,name,manager_id,email,age FROM user WHERE (date_format(create_time,'%Y-%m-%d')=? AND manager_id IN (select id from user where name like '王%')) 
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    /**
     * SQL 注入的例子：
     */
    @Test
    public void selectByWrapper2_1(){
    	QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    	queryWrapper.apply("date_format(create_time,'%Y-%m-%d')=2019-02-14 or true=true");
    	//SELECT id,name,manager_id,email,age FROM user WHERE (date_format(create_time,'%Y-%m-%d')=? AND manager_id IN (select id from user where name like '王%')) 
    	List<User> userList = userMapper.selectList(queryWrapper);
    	userList.forEach(System.out::println);
    }
    
    
    /**
          * 名字为王姓，（年龄小于40或者邮箱不为空）
     */
    @Test
    public void selectByWrapper3(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name","王").and(wq-> wq.lt("age",40).or().isNotNull("email"));
        //SELECT id,name,manager_id,email,age FROM user WHERE (name LIKE ? AND ( (age < ? OR email IS NOT NULL) )) 
        //王%(String), 40(Integer)
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    
    /**
          * 名字为王姓，（年龄小于40，并且年龄大于20，并且邮箱不为空）
     */
    @Test
    public void selectWrapper4(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王").and(wq -> wq.between("age", 20, 40).and(wqq -> wqq.isNotNull("email")));
        //SELECT id,name,manager_id,email,age FROM user WHERE (name LIKE ? AND ( (age BETWEEN ? AND ? AND ( (email IS NOT NULL) )) )) 
        //王%(String), 20(Integer), 40(Integer)
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    /**
     * nested 正常嵌套 不带 AND 或者 OR
          * （年龄小于40或者邮箱不为空）并且名字为王姓
          * （age<40 or email is not null）and name like '王%'
     */
    @Test
    public void selectWrapper5(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email")).likeRight("name","王");
        //SELECT id,name,manager_id,email,age FROM user WHERE (( (age < ? OR email IS NOT NULL) ) AND name LIKE ?) 
        //40(Integer), 王%(String)
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    
    /**
     * in
          * 年龄为30,31,35,34的员工
     */
    @Test
    public void selectWrapper6(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30,31,34,35));
        //SELECT id,name,manager_id,email,age FROM user WHERE (age IN (?,?,?,?)) 
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    
    /**
     * last 有SQL注入的风险！！！
          * 只返回满足条件的一条语句即可
     * limit 1
     */
    @Test
    public void selectWrapper7(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30,31,34,35)).last("limit 1");
        //SELECT id,name,manager_id,email,age FROM user WHERE (age IN (?,?,?,?)) limit 1 
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    
    /**
          * 查询指定部分列
          * 查找为王姓的员工的姓名和年龄
     */
    @Test
    public void selectWrapper8(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name","age").likeRight("name","王");
        // SELECT name,age FROM user WHERE (name LIKE ?) 
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    /**
          * 使用过滤器查询指定列
          * 查询所有员工信息除了创建时间和员工ID列
     */
    @Test
    public void selectWrapper9(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(User.class,info->!info.getColumn().equals("create_time")
                &&!info.getColumn().equals("manager_id"));
        //SELECT id,name,email,age FROM user 
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    /**
          * 根据实体对象查询
     */
    @Test
    public void selectWrapper10(){
        User user = new User();
        user.setName("刘红雨");
        user.setAge(32);
        QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
        //SELECT id,name,manager_id,email,age FROM user WHERE name=? AND age=? 
        //刘红雨(String), 32(Integer)
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

    /**
     * 	在实体类要查询的字段上面增加注解 可以模糊查询
     * @TableField(condition = SqlCondition.LIKE)
     *  private String name;
     */
    @Test
    public void selectWrapper10_1(){
        User user = new User();
        user.setName("红");
        user.setAge(32);
        QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
        //SELECT id,name,manager_id,email,age FROM user WHERE name LIKE CONCAT('%',?,'%') AND age=? 
        //红(String), 32(Integer)
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    
    /**
     *  Lambda条件构造器
     *  LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda();
        LambdaQueryWrapper<User> lambdaQueryWrapper1 = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<User> lambdaQueryWrapper2 = Wrappers.lambdaQuery();
     */
    
    /**
          * 查询名字中包含‘雨’并且年龄小于40的员工信息
     */
    @Test
    public void lambdaSelect(){
        LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
        lambdaQueryWrapper.like(User::getName,"雨").lt(User::getAge,40);
        //SELECT id,name,manager_id,email,age FROM user WHERE (name LIKE ? AND age < ?) 
        //%雨%(String), 40(Integer)
        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        userList.forEach(System.out::println);
    }
    
    
    /**
     * MyBatisPlus提供了第四种函数式编程方式，不用每次都传mapper 
          * 查询名字中包含‘雨’并且年龄小于40的员工信息
     */
	@Test
	public void lambdaSelect2(){
		List<User> userList = 
				new LambdaQueryChainWrapper<User>(userMapper)
				.like(User::getName, "雨")
				.lt(User::getAge,40)
				.list();
	   //SELECT id,name,manager_id,email,age FROM user WHERE (name LIKE ? AND age < ?) 
	   //%雨%(String), 40(Integer)
	   userList.forEach(System.out::println);
	}    
    
	
	/** 
	  * 分页查询 先查询总条数,在分页查询数据
	  * 查询年龄大于20 的用户信息，并以每页容量为两条分页的形式返回。
	 */
    @Test
    public void selectPage(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age",20);
        //设置当前页和页容量
        Page<User> page = new Page<>(1, 2);
        IPage<User> userIPage = userMapper.selectPage(page, queryWrapper);
        //SELECT COUNT(1) FROM user WHERE (age >= ?) 
        //SELECT id,name,manager_id,email,age FROM user WHERE (age >= ?) LIMIT ?,? 
        //20(Integer), 0(Long), 2(Long)
        System.out.println("总页数："+userIPage.getPages());//4
        System.out.println("总记录数："+userIPage.getTotal());//7
        userIPage.getRecords().forEach(System.out::println);
    }
    
	/** 
	  * 分页查询 不查询总条数
	  * 查询年龄大于20 的用户信息，并以每页容量为两条分页的形式返回。
	 */
    @Test
    public void selectPage_1(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age",20);
        //设置当前页和页容量,第三个参数为false时，不会查询总记录数。
        Page<User> page = new Page<>(1, 2,false);
        IPage<User> userIPage = userMapper.selectPage(page, queryWrapper);
        //SELECT id,name,manager_id,email,age FROM user WHERE (age >= ?) LIMIT ?,? 
        //20(Integer), 0(Long), 2(Long)
        //由于没有查询总条数,总页数和总记录数会为0
        System.out.println("总页数："+userIPage.getPages());//0
        System.out.println("总记录数："+userIPage.getTotal());//0
        userIPage.getRecords().forEach(System.out::println);
    }
    
    /**
          * 查询全部 AR模式（直接通过实体类完成对数据的增删改查。）
     */
    @Test
    public void test(){
        User user = new User();
        //SELECT id,name,manager_id,email,age FROM user 
//        user.selectAll().forEach(System.out::println);
    }
    
    
    
}
